package impl;

import java.io.ObjectInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import pojo.Book;
import util.DBUtil;
import dao.BookDao;

public class BookDaoImpl implements BookDao{

	public int addBook(Book b) {
		String sql="insert into book(id,bname,pub_date,author,price,is_new,publisher)values(seqbk1.nextval,?,?,?,?,?,?)";
		Connection conn=null;
		PreparedStatement pst =null;
		ResultSet rs = null;
		int m = 0;
		try {
			conn=DBUtil.createConnection();
			pst=conn.prepareStatement(sql);
			pst.setString(1, b.getBname());
			pst.setDate(2, new java.sql.Date(b.getPubDate().getTime()));
			pst.setString(3, b.getAuthor());
			pst.setDouble(4, b.getPrice());
			pst.setBoolean(5, b.isNew());
			pst.setString(6, b.getPublisher());
			m = pst.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			DBUtil.close(conn, pst, null);
		}
		System.out.println(m);
		return m;
	}

	public int deleteBook(Integer id) {
		String sql="delete book where id = ?";
		Connection conn=null;
		PreparedStatement pst =null;
		ResultSet rs = null;
		int m = 0;
		try {
			conn=DBUtil.createConnection();
			pst=conn.prepareStatement(sql);
			pst.setInt(1, id.intValue());
			m = pst.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			DBUtil.close(conn, pst, null);
		}
		System.out.println(m);
		return m;
	}

	public int updateBook(Book b) {
		String sql="update book set bname=?,pub_date=?,author=?,price=?,is_new=?,publisher=? where id=?";
		Connection conn=null;
		PreparedStatement pst =null;
		ResultSet rs = null;
		int m = 0;
		try {
			conn=DBUtil.createConnection();
			pst=conn.prepareStatement(sql);
			pst.setString(1, b.getBname());
			pst.setDate(2, new java.sql.Date(b.getPubDate().getTime()));
			pst.setString(3, b.getAuthor());
			pst.setDouble(4, b.getPrice());
			pst.setBoolean(5, b.isNew());
			pst.setString(6, b.getPublisher());
			pst.setInt(7, b.getId());
			m = pst.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			DBUtil.close(conn, pst, null);
		}
		System.out.println(m);
		return m;
	}

	public Book loadBook(Integer id) {
		String sql="select * from book where id = ?";
		PreparedStatement pst =null;
		ResultSet rs = null;
		Connection conn = null;
		Book b = new Book();
		int m =0;
		try {
			conn=DBUtil.createConnection();
			pst=conn.prepareStatement(sql);
			pst.setInt(1, id);
			rs=pst.executeQuery();
			while(rs.next()){
				b.setBname(rs.getString("bname"));//c.setCname(rs.getString("cname"));//String cname = rs.getString("cname");
				b.setId(rs.getInt("id"));//c.setId(rs.getInt("id"));//int id = rs.getInt("id");
				b.setPubDate(new Date(rs.getDate("pub_date").getTime()));//c.setSal(rs.getDouble("sal"));//double sal = rs.getDouble("sal");
				b.setAuthor(rs.getString("author"));//c.setSex(rs.getBoolean("sex"));//boolean sex = rs.getBoolean("sex");
				b.setPrice(rs.getDouble("price"));//c.setBirth(new Date(rs.getDate("birth").getTime()));//Date birth = new Date(rs.getDate("birth").getTime());
				b.setNew(rs.getBoolean("is_new"));//System.out.println(id+"\t"+cname+"\t"+sal+"\t"+sex+"\t"+birth);
				b.setPublisher(rs.getString("publisher"));
			}
			m=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			DBUtil.close(conn, pst, rs);
		}
		System.out.println(b.getId()+"  "+b.getBname());
		return b;
	}

	public List<Book> findAll() {
		List<Book> bl = new ArrayList<Book>();
		String sql="select * from book";
		PreparedStatement pst =null;
		ResultSet rs = null;
		Connection conn = null;
		Book b = new Book();
		int m =0;
		try {
			conn=DBUtil.createConnection();
			pst=conn.prepareStatement(sql);
			rs=pst.executeQuery();
			while(rs.next()){
				b.setBname(rs.getString("bname"));//c.setCname(rs.getString("cname"));//String cname = rs.getString("cname");
				b.setId(rs.getInt("id"));//c.setId(rs.getInt("id"));//int id = rs.getInt("id");
				b.setPubDate(new Date(rs.getDate("pub_date").getTime()));//c.setSal(rs.getDouble("sal"));//double sal = rs.getDouble("sal");
				b.setAuthor(rs.getString("author"));//c.setSex(rs.getBoolean("sex"));//boolean sex = rs.getBoolean("sex");
				b.setPrice(rs.getDouble("price"));//c.setBirth(new Date(rs.getDate("birth").getTime()));//Date birth = new Date(rs.getDate("birth").getTime());
				b.setNew(rs.getBoolean("is_new"));//System.out.println(id+"\t"+cname+"\t"+sal+"\t"+sex+"\t"+birth);
				b.setPublisher(rs.getString("publisher"));
				bl.add(b);
				System.out.println(b.getId()+"  "+b.getBname());
			}
			m=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			DBUtil.close(conn, pst, rs);
		}
		return bl;
	}

	public int addBooksFromFile(ObjectInputStream oin) {
		String sql="insert into book(id,bname,pub_date,author,price,is_new,publisher)values(seqbk1.nextval,?,?,?,?,?,?)";
		Book b;
		Connection conn=null;
		PreparedStatement pst =null;
		ResultSet rs = null;
		int m = 0;
		try {
			conn=DBUtil.createConnection();
			pst=conn.prepareStatement(sql);
			while((b=(Book)oin.readObject())!=null){
				pst.setString(1, b.getBname());
				pst.setDate(2, new java.sql.Date(b.getPubDate().getTime()));
				pst.setString(3, b.getAuthor());
				pst.setDouble(4, b.getPrice());
				pst.setBoolean(5, b.isNew());
				pst.setString(6, b.getPublisher());
				m += pst.executeUpdate();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			DBUtil.close(conn, pst, null);
		}
//		System.out.println(m);
		return m;
	}

	public int addBooksFromFileWithBatch(ObjectInputStream oin) {
		String sql="insert into book(id,bname,pub_date,author,price,is_new,publisher)values(seqbk1.nextval,?,?,?,?,?,?)";
		Book b;
		Connection conn=null;
		PreparedStatement pst =null;
		ResultSet rs = null;
		int i = 0;
		int m = 0;
		try {
			conn=DBUtil.createConnection();
			pst=conn.prepareStatement(sql);
			while((b=(Book)oin.readObject())!=null){
				i+=1;
				pst.setString(1, b.getBname());
				pst.setDate(2, new java.sql.Date(b.getPubDate().getTime()));
				pst.setString(3, b.getAuthor());
				pst.setDouble(4, b.getPrice());
				pst.setBoolean(5, b.isNew());
				pst.setString(6, b.getPublisher());
				pst.addBatch();
				if(i%1000==0){
					 pst.executeBatch(); 
					 pst.clearBatch();  
				}
				m+=1;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			DBUtil.close(conn, pst, null);
		}
		return m;
	}

}
